﻿using Dapper;
using Domain.Entity;
using Domain.Entity.bs;
using Domain.Entity.sys;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class ProductTypeRepository : IProductTypeRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";

        //添加货品类型
        public async Task<int> AddProductType(ProductType productType)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[bs_product_type](type_code, type_name, type_pid, type_sort, type_status, create_name, create_time, remark)
                                values('{productType.type_code}','{productType.type_name}',{productType.type_pid},{productType.type_sort},{productType.type_status},'{productType.create_name}','{productType.create_time}','{productType.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除货品类型
        public async Task<int> DeleteProductType(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[bs_product_type] set is_delete=1 where type_id={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //查询所有货品类型
        public async Task<List<ProductType>> GetAllProductTypes()
        {
            using(SqlConnection sqlConnection=new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = "select type_id,type_name,type_pid from [dbo].[bs_product_type] where is_delete=0";
                return sqlConnection.Query<ProductType>(sql).ToList();
            }
        }

        //分页查询货品类型
        public async Task<FenYe<ProductType>> GetProductType(FenYe<ProductType> fenYe, string? name, int? status, string? createname, string? starttime, string? endtime)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "type_id");
                param.Add("@tabelName", "bs_product_type");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 ";
                if (!string.IsNullOrEmpty(name))
                    sqlparam += $" and type_name like '%{name}%'";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  type_status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  create_time between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<ProductType> list = sqlConnection.Query<ProductType>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }

        //根据id查询货品类型
        public async Task<ProductType> GetProductTypeById(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[bs_product_type] where type_id={id}";
                return await sqlConnection.QueryFirstAsync<ProductType>(sql);
            }
        }

        //修改货品类型
        public async Task<int> UpdateProductType(ProductType productType)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[bs_product_type] set type_code='{productType.type_code}',type_name='{productType.type_name}',type_pid={productType.type_pid},type_sort={productType.type_sort},type_status={productType.type_status},update_name='{productType.update_name}',update_time='{productType.update_time}',remark='{productType.remark}' where type_id={productType.type_id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }
    }
}
